package com.accesshollywood.SQL;

import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import sqlUtility.SQLUtility;

import com.accesshollywood.grfxOrdering.GRFXOrdersMain;
import com.accesshollywood.grfxOrdering.Dialogs.CommDialog;
import com.accesshollywood.grfxOrdering.basics.Destinations;
import com.accesshollywood.grfxOrdering.basics.GraphicTypes;
import com.accesshollywood.grfxOrdering.basics.NamedList;
import com.accesshollywood.grfxOrdering.basics.Users;
import com.accesshollywood.indexables.Destination;
import com.accesshollywood.indexables.GraphicType;
import com.accesshollywood.indexables.GrfxOrder;
import com.accesshollywood.indexables.PreApprovedText;
import com.accesshollywood.indexables.ShowName;
import com.accesshollywood.indexables.User;
import com.accesshollywood.indexables.ViewFilterPreset;
import com.accesshollywood.indexables.items.GrfxItem;
import com.accesshollywood.notifications.Notify;

public class GFXSQL extends SQLUtility {
	public static ArrayList<User>	connectedUsers	= new ArrayList<User>();
	public static ArrayList<String>	connectedIPs	= new ArrayList<String>();

	public static void registerConnection() {
		InetAddress ownIP;
		try {
			ownIP = InetAddress.getLocalHost();
			idIP = ownIP.getHostAddress();
			Integer[] connections = getInt("Connection", "IP", "IP = '" + idIP + "'");
			int connectionStatus;
			if (connections == null) {
				connectionStatus = addString("Connection", "IP", idIP);
				// alterInt("Connection", "idPerson", Users.currentUser.idPerson, "IP = '" + idIP + "'");
			} else {
				connectionStatus = connections[0];
			}
		} catch (UnknownHostException e) {
			e.printStackTrace();
		}
	}

	public static void unregisterConnection() {
		disconnectDataBase();
	}

	public static void addOrderSimple(String summary, String artist, String dueDate, String description) throws SQLException {
		int newOrder = addString("gfxOrder", "summary", "" + summary);

		Integer idArtist = getInt("canArtist", "idPerson", "firstName = '" + artist + "'")[0];
		alterInt("gfxOrder", "idArtist", idArtist, newOrder);
		alterString("gfxOrder", "dueDate", "" + dueDate, newOrder);
		alterString("gfxOrder", "notes", "" + description, newOrder);
	}
/*
	public static void addOrder(GrfxOrder currOrder) {
		try {
			int itemListID = putItems(currOrder.getItems());

			int newOrder = addInt("gfxOrder", "idItemList", itemListID);

			alterInt("gfxOrder", "idShowName", currOrder.getShowName().idShowName, newOrder);
			alterString("gfxOrder", "summary", currOrder.getSummary().toString(), newOrder);
			alterString("gfxOrder", "block", currOrder.getBlock().str, newOrder);
			alterString("gfxOrder", "dueDate", currOrder.getDueDate().toSQL(), newOrder);
			alterString("gfxOrder", "creationDate", new SQLDate().toSQL(), newOrder);
			alterInt("gfxOrder", "idGraphicType", currOrder.getGraphicType().idGraphicType, newOrder);

			alterBoolean("gfxOrder", "needsResearch", currOrder.needsResearch(), newOrder);
			alterBoolean("gfxOrder", "needsPhotoEdit", currOrder.needsPhotoEdit(), newOrder);
			alterBoolean("gfxOrder", "needsFootage", currOrder.needsFootage(), newOrder);
			alterBoolean("gfxOrder", "needsLegal", currOrder.needsLegal(), newOrder);

			alterString("gfxOrder", "notes", currOrder.getNotesSQL(), newOrder);

			alterInt("gfxOrder", "idOrderedBy", currOrder.getOrderedBy().idPerson, newOrder);
			alterInt("gfxOrder", "idArtist", currOrder.getArtist().idPerson, newOrder);
			alterInt("gfxOrder", "idResearcher", currOrder.getResearcher().idPerson, newOrder);
			alterInt("gfxOrder", "idPhotoEdit", currOrder.getPhotoEdit().idPerson, newOrder);
			alterInt("gfxOrder", "idFootage", currOrder.getFootage().idPerson, newOrder);
			alterInt("gfxOrder", "idLegal", currOrder.getLegal().idPerson, newOrder);
			alterInt("gfxOrder", "idProducer", currOrder.getProducer().idPerson, newOrder);

			// Trace.info(newOrder);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void addOrder(GrfxOrder currOrder, int idOrder) {
		try {
			int itemListID = putItems(currOrder.getItems());

			int newOrder = addInt("gfxOrder", "idOrder", idOrder);

			alterInt("gfxOrder", "idItemList", itemListID, newOrder);

			alterString("gfxOrder", "summary", currOrder.getSummary().toString(), newOrder);
			alterString("gfxOrder", "block", currOrder.getBlock().str, newOrder);

			alterString("gfxOrder", "dueDate", currOrder.getDueDate().toSQL(), newOrder);
			alterString("gfxOrder", "creationDate", new SQLDate().toSQL(), newOrder);

			alterBoolean("gfxOrder", "needsResearch", currOrder.needsResearch(), newOrder);
			alterBoolean("gfxOrder", "needsPhotoEdit", currOrder.needsPhotoEdit(), newOrder);
			alterBoolean("gfxOrder", "needsFootage", currOrder.needsFootage(), newOrder);
			alterBoolean("gfxOrder", "needsLegal", currOrder.needsLegal(), newOrder);

			alterBoolean("gfxOrder", "apprResearch", currOrder.isApprResearch(), newOrder);
			alterBoolean("gfxOrder", "apprPhotoEdit", currOrder.isApprPhotoEdit(), newOrder);
			alterBoolean("gfxOrder", "apprFootage", currOrder.isApprFootage(), newOrder);
			alterBoolean("gfxOrder", "apprLegal", currOrder.isApprLegal(), newOrder);

			alterString("gfxOrder", "notes", currOrder.getNotesSQL(), newOrder);

			alterInt("gfxOrder", "idOrderedBy", currOrder.getOrderedBy().idPerson, newOrder);
			alterInt("gfxOrder", "idArtist", currOrder.getArtist().idPerson, newOrder);
			alterInt("gfxOrder", "idResearcher", currOrder.getResearcher().idPerson, newOrder);
			alterInt("gfxOrder", "idPhotoEdit", currOrder.getPhotoEdit().idPerson, newOrder);
			alterInt("gfxOrder", "idFootage", currOrder.getFootage().idPerson, newOrder);
			alterInt("gfxOrder", "idLegal", currOrder.getLegal().idPerson, newOrder);
			alterInt("gfxOrder", "idProducer", currOrder.getProducer().idPerson, newOrder);

			alterInt("gfxOrder", "idGraphicType", currOrder.getGraphicType().idGraphicType, newOrder);
			alterInt("gfxOrder", "idDestination", currOrder.getDestination().idDestination, newOrder);
			alterInt("gfxOrder", "idShowName", currOrder.getShowName().idShowName, newOrder);

			alterBoolean("gfxOrder", "complete", currOrder.isComplete(), newOrder);
			alterBoolean("gfxOrder", "closed", currOrder.isClosed(), newOrder);
			alterBoolean("gfxOrder", "archived", currOrder.isArchived(), newOrder);

			// Trace.info(newOrder);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
*/
	public static void moveToArchive(int idOrder) {
//		GrfxOrder archiveMe = getOrder(idOrder);
//		archiveMe.setArchived(true);//Quiet
		setArchiveAsActiveDB(true);
//		addOrder(archiveMe, archiveMe.getId());
		setArchiveAsActiveDB(false);
		try {
			removeOrder(idOrder);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		GRFXOrdersMain.gui.refresh(true);
	}

	public static void moveFromArchive(int idOrder) {
		setArchiveAsActiveDB(true);
//		GrfxOrder unArchiveMe = getOrder(idOrder);
//		unArchiveMe.setArchived(false);//Quiet
		setArchiveAsActiveDB(false);
//		addOrder(unArchiveMe, unArchiveMe.getId());
		setArchiveAsActiveDB(true);
		try {
			removeOrder(idOrder);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		setArchiveAsActiveDB(false);
		GRFXOrdersMain.gui.refresh(true);
	}

	public static void removeOrder(int idOrder) throws SQLException {
		int idItemList = (int) getInt("gfxOrder", "idItemList", "idOrder = " + idOrder)[0];
		Integer[] idItems = getInt("itemList", "idItem", "idItemList = " + idItemList);
		if (idItems != null) {
			for (int i = 0; i < idItems.length; i++) {
				int idStillList = (int) getInt("item", "idStillList", "idItem = " + idItems[i])[0];
				int idFootageList = (int) getInt("item", "idFootageList", "idItem = " + idItems[i])[0];
				Integer[] idStills = getInt("stillList", "idStill", "idStillList = " + idItemList);
				Integer[] idFootage = getInt("footageList", "idFootage", "idFootageList = " + idItemList);
				if (idStills != null) {
					for (int s = 0; s < idStills.length; s++) {
						removeElement("still", "idStill = " + idStills[s]);
						removeElement("stillList", "idStill = " + idStills[s]);
					}
				}
				if (idFootage != null) {
					for (int f = 0; f < idFootage.length; f++) {
						removeElement("footage", "idFootage = " + idFootage[f]);
						removeElement("footageList", "idFootage = " + idFootage[f]);
					}
				}
				removeElement("stillList", "idStillList = " + idStillList);
				removeElement("footageList", "idFootageList = " + idFootageList);
				removeElement("item", "idItem = " + idItems[i]);
				removeElement("itemList", "idItem = " + idItems[i]);
			}
		}
		removeElement("gfxOrder", "idOrder = " + idOrder);
	}
/*
	public static GrfxOrder getOrder(int idOrder) {
		GrfxOrder currOrder = null;
		try {
			ResultSet rawOrder = SQLUtility.getResultSetFromActiveDB("gfxOrder", "*", "idOrder = " + idOrder);
			if (rawOrder.next()) {
				currOrder = new GrfxOrder(rawOrder);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return currOrder;
	}

	public static int putItems(IndexedSQL<GrfxItemList> items) throws SQLException {
		ArrayList<Integer> itemIDs = items.getElement().toItemIDarray();

		int newItemListID = getNextAvailableID("itemList");
		for (int i = 0; i < itemIDs.size(); i++) {
			addInts("itemList", new String[] { "idItemList", "idItem" }, new Integer[] { newItemListID, itemIDs.get(i) });
		}
		return newItemListID;
	}

	public static int putItem(GrfxItem currItem, int idItem) throws SQLException {
		ArrayList<Integer> textIDs = new ArrayList<Integer>();
		ArrayList<Integer> stillIDs = new ArrayList<Integer>();
		ArrayList<Integer> footageIDs = new ArrayList<Integer>();

		int newTextListID = -1;
		int newStillListID = -1;
		int newFootageListID = -1;

		ArrayList<String> newItemColumns = new ArrayList<String>();
		ArrayList<Integer> newItemValues = new ArrayList<Integer>();

		if (idItem > 0) {
			newItemColumns.add("idItem");
			newItemValues.add(idItem);
		}

		if (currItem.getTextList().size() > 0) {
			for (int j = 0; j < currItem.getTextList().size(); j++) {
				int newTextID = addString("text", "text", ((IndexedSQL<String>) currItem.getTextList().get(j)).getElement());
				textIDs.add(newTextID);
			}
			newTextListID = getNextAvailableID("textList");
			currItem.getTextList().id = newTextListID;
			for (int j = 0; j < textIDs.size(); j++) {
				addInts("textList", new String[] { "idTextList", "idText" }, new Integer[] { newTextListID, textIDs.get(j) });
			}
			newItemColumns.add("idTextList");
			newItemValues.add(newTextListID);
		}

		if (currItem.getStillList().size() > 0) {
			for (int j = 0; j < currItem.getStillList().size(); j++) {
				Still currStill = (Still) currItem.getStillList().get(j);
				if (currStill.getIdStill() > 0) {
					alterString("still", "description", currStill.getDescription(), "idStill = " + currStill.getIdStill());
				} else {
					currStill.setIdStill(addString("still", "description", currStill.getDescription()));
				}
				stillIDs.add(currStill.getIdStill());
			}
			newStillListID = getNextAvailableID("stillList");
			currItem.getStillList().id = newStillListID;
			for (int j = 0; j < stillIDs.size(); j++) {
				addInts("stillList", new String[] { "idStillList", "idStill" }, new Integer[] { newStillListID, stillIDs.get(j) });
				// alterString("stillList", "path", "'" + currItem.stillList.name + "'", newStillListID);
				GFXSQL.setStillListPath(currItem.getStillList().name, newStillListID);
			}
			newItemColumns.add("idStillList");
			newItemValues.add(newStillListID);
		}

		if (currItem.getFootageList().size() > 0) {
			for (int j = 0; j < currItem.getFootageList().size(); j++) {
				Footage currFootage = (Footage) currItem.getFootageList().get(j);
				if (currFootage.getIdFootage() > 0) {
					alterString("footage", "description", currFootage.getDescription(), "idFootage = " + currFootage.getIdFootage());
				} else {
					currFootage.setIdFootage(addString("footage", "description", currFootage.getDescription()));
				}
				footageIDs.add(currFootage.getIdFootage());
			}
			newFootageListID = getNextAvailableID("footageList");
			currItem.getFootageList().id = newFootageListID;
			for (int j = 0; j < footageIDs.size(); j++) {
				addInts("footageList", new String[] { "idFootageList", "idFootage" }, new Integer[] { newFootageListID, footageIDs.get(j) });
				// alterString("footageList", "path", "'" + currItem.footageList.name + "'", newFootageListID);
				GFXSQL.setFootageListPath(currItem.getFootageList().toString(), newFootageListID);
			}
			newItemColumns.add("idFootageList");
			newItemValues.add(newFootageListID);
		}

		// int newItemID = getNextAvailableID("item");

		idItem = addInts("item", newItemColumns.toArray(), newItemValues.toArray());
		currItem.setIdItem(idItem);

		alterString("item", "FileName", currItem.getFilename().toString(), idItem);

		if (currItem.getTextList().size() > 0) {
			alterBoolean("item", "needsResearch", true, currItem.getIdItem());
		}
		if (currItem.getStillList().size() > 0) {
			alterBoolean("item", "needsPhotoEdit", true, currItem.getIdItem());
		}
		if (currItem.getFootageList().size() > 0) {
			alterBoolean("item", "needsFootage", true, currItem.getIdItem());
		}

		return idItem;
	}
*/
	public static int alterItem(GrfxItem currItem) throws SQLException {
		removeItem(currItem);
		int newID = -1;
//		int newID = putItem(currItem, currItem.getId());

		return newID;
	}

	public static void removeItem(GrfxItem currItem) throws SQLException {
		int idItem = currItem.getId();

		// Trace.info(currItem.textList.id + "   " + currItem.stillList.id + "   " + currItem.footageList.id);

		removeText(currItem.getTextList().getId());
		removeStills(currItem.getStillList().getId());
		removeFootage(currItem.getFootageList().getId());
		SQLUtility.removeElement("item", "idItem = " + idItem);
	}
/*
	public static IndexedSQL<GrfxItemList> getItems(int idItemList) throws SQLException{
		GrfxItemList itemSet = new GrfxItemList();
		IndexedSQL<GrfxItemList> result = new IndexedSQL<GrfxItemList>();

		itemSet.setElement(idItemList, true);
		result.setID(idItemList);
		result.setElement(itemSet, false);
		
		return result;
	}
	
	public static LinkedHashMap<Integer, GrfxItem> getItems2(int idItemList) throws SQLException {
		LinkedHashMap<Integer, GrfxItem> itemList = new LinkedHashMap<Integer, GrfxItem>();
		ResultSet rawItemIDs = SQLUtility.getResultSetFromActiveDB("itemList", "idItem", "idItemList = " + idItemList);
		while (rawItemIDs.next()) {
			int idItem = rawItemIDs.getInt("idItem");
			ResultSet rawItem = SQLUtility.getResultSetFromActiveDB("item", "*", "idItem = " + idItem);
			while (rawItem.next()) {
				GrfxItem newItem = new GrfxItem();
				newItem.setIdItem(idItem);
				newItem.setFilename(new NamedList());
				newItem.getFilename().addStr(rawItem.getString("fileName"));

				newItem.setTextList(getText(rawItem.getInt("idTextList")));
				int idStillList = rawItem.getInt("idStillList");
				newItem.setStillList(getStills(idStillList));
				if (newItem.getStillList().name != null && !newItem.getStillList().name.equals("")) {
					// Trace.info("***************************************************************" + newItem.stillList.name);
					newItem.setApprPhotoEdit(true);
				}
				newItem.setFootageList(getFootage(rawItem.getInt("idFootageList")));

				newItem.setNeedsResearch(rawItem.getBoolean("needsResearch"));
				newItem.setNeedsPhotoEdit(rawItem.getBoolean("needsPhotoEdit"));
				newItem.setNeedsFootage(rawItem.getBoolean("needsFootage"));
				newItem.setNeedsLegal(rawItem.getBoolean("needsLegal"));

				newItem.setApprResearch(rawItem.getBoolean("apprResearch"));
				newItem.setApprPhotoEdit(rawItem.getBoolean("apprPhotoEdit"));
				newItem.setApprFootage(rawItem.getBoolean("apprFootage"));
				newItem.setApprLegal(rawItem.getBoolean("apprLegal"));
				itemList.put(newItem.getId(), newItem);
			}
		}
		return itemList;
	}

	public static NamedList getText(int idTextList) throws SQLException {
		NamedList textList = new NamedList();
		textList.id = idTextList;
		textList.name = "TEXT";
		ResultSet rawTextIDs = SQLUtility.getResultSetFromActiveDB("textList", "idText", "idTextList = " + idTextList);
		while (rawTextIDs.next()) {
			// Trace.info(rawTextIDs.getInt("idText"));
			int idText = rawTextIDs.getInt("idText");
			ResultSet rawItem = SQLUtility.getResultSetFromActiveDB("text", "*", "idText = " + idText);
			while (rawItem.next()) {
				IndexedSQL<String> newText = new IndexedSQL<String>(idText, rawItem.getString("text"));
				textList.add(newText);
				// String newText = "";
				// newText = rawItem.getString("text");
				// textList.add(newText);
			}
		}
		return textList;
	}

	public static NamedList getStills(int idStillList) throws SQLException {
		NamedList stillList = new NamedList();
		stillList.id = idStillList;
		stillList.name = SQLUtility.getString("stillList", "path", "idStillList = " + idStillList)[0];
		ResultSet rawStillIDs = SQLUtility.getResultSetFromActiveDB("stillList", "idStill", "idStillList = " + idStillList);
		while (rawStillIDs.next()) {
			// Trace.info(rawStillIDs.getInt("idStill"));
			int idStill = rawStillIDs.getInt("idStill");
			ResultSet rawStill = SQLUtility.getResultSetFromActiveDB("still", "*", "idStill = " + idStill);
			while (rawStill.next()) {
				Still newStill = new Still();
				newStill.setIdStill(rawStill.getInt("idStill"));
				newStill.setDescription(rawStill.getString("description"));
				// newStill.path = rawStill.getString("path");
				stillList.add(newStill);
			}
		}
		return stillList;
	}

	public static NamedList getFootage(int idFootageList) throws SQLException {
		NamedList footageList = new NamedList();
		footageList.id = idFootageList;
		footageList.name = SQLUtility.getString("footageList", "path", "idFootageList = " + idFootageList)[0];
		ResultSet rawFootageIDs = SQLUtility.getResultSetFromActiveDB("footageList", "idFootage", "idFootageList = " + idFootageList);
		while (rawFootageIDs.next()) {
			// Trace.info(rawFootageIDs.getInt("idFootage"));
			int idFootage = rawFootageIDs.getInt("idFootage");
			ResultSet rawFootage = SQLUtility.getResultSetFromActiveDB("footage", "*", "idFootage = " + idFootage);
			while (rawFootage.next()) {
				Footage newFootage = new Footage();
				newFootage.setIdFootage(rawFootage.getInt("idFootage"));
				newFootage.setDescription(rawFootage.getString("description"));
				// newFootage.path = rawFootage.getString("path");
				footageList.add(newFootage);
			}
		}
		return footageList;
	}
*/
	public static void removeText(int idTextList) throws SQLException {
		ResultSet rawTextIDs = SQLUtility.getResultSetFromActiveDB("textList", "idText", "idTextList = " + idTextList);
		while (rawTextIDs.next()) {
			// Trace.info(rawTextIDs.getInt("idText"));
			int idText = rawTextIDs.getInt("idText");
			SQLUtility.removeElement("text", "idText = " + idText);
			SQLUtility.removeElement("textList", "idText = " + idText);
		}
	}

	public static void removeStills(int idStillList) throws SQLException {
		ResultSet rawStillIDs = SQLUtility.getResultSetFromActiveDB("stillList", "idStill", "idStillList = " + idStillList);
		while (rawStillIDs.next()) {
			// Trace.info(rawStillIDs.getInt("idStill"));
			int idStill = rawStillIDs.getInt("idStill");
			SQLUtility.removeElement("still", "idStill = " + idStill);
		}
		SQLUtility.removeElement("stillList", "idStillList = " + idStillList);
	}

	public static void removeFootage(int idFootageList) throws SQLException {
		ResultSet rawFootageIDs = SQLUtility.getResultSetFromActiveDB("footageList", "idFootage", "idFootageList = " + idFootageList);
		while (rawFootageIDs.next()) {
			// Trace.info(rawFootageIDs.getInt("idFootage"));
			int idFootage = rawFootageIDs.getInt("idFootage");
			SQLUtility.removeElement("footage", "idFootage = " + idFootage);
		}
		SQLUtility.removeElement("footageList", "idFootageList = " + idFootageList);
	}

	public static void setStillListPath(String UNCfilePath, int idStillList) {
		alterString("stillList", "path", UNCfilePath, idStillList);
	}

	public static void setFootageListPath(String UNCfilePath, int idFootageList) {
		alterString("footageList", "path", UNCfilePath, idFootageList);
	}

	public static void setAppr(GrfxOrder currOrder, String field, boolean value) {
		int idOrder = currOrder.getId();
		GFXSQL.alterBoolean("gfxOrder", field, value, idOrder);
	}

	public static boolean grabOrder(User grabbingUser, GrfxOrder currOrder, boolean force) {
		int idJob;
		int idOrder = currOrder.getId();
		String jobColumn = "";
		if (Users.currentUser.isArtist()) {
			jobColumn = "idArtist";
		} else if (Users.currentUser.isResearch()) {
			jobColumn = "idResearcher";
		} else if (Users.currentUser.isPhotoEdit()) {
			jobColumn = "idPhotoEdit";
		} else if (Users.currentUser.isLegal()) {
			jobColumn = "idLegal";
		}
		idJob = GFXSQL.getInt("gfxOrder", jobColumn, "idOrder = " + idOrder)[0];
		if (force || idJob < 20 || idJob == Users.currentUser.getId()) {
			GFXSQL.alterInt("gfxOrder", jobColumn, grabbingUser.getId(), idOrder);
			Notify.orderAssigned(currOrder, grabbingUser, Users.currentUser);
			return true;
		} else {
			JOptionPane.showMessageDialog(GRFXOrdersMain.gui, "Someone already Grabbed this order!");
		}
		return false;
	}

	public static boolean closeOrder(int idOrder, boolean isClosed) {
		GFXSQL.alterBoolean("gfxOrder", "closed", isClosed, idOrder);
		return true;
	}

	public static void archiveOrder(int idOrder, boolean isArchived) {
		if (isArchived) {
			moveToArchive(idOrder);
		} else {
			moveFromArchive(idOrder);
		}
		/*
		 * // at some point it would be nice to remove this order from gfxOrder
		 * // table and
		 * // put it into archive. Also grab any folders in version and set them
		 * // aside
		 * // for archiving
		 * try {
		 * GFXSQL.alterBoolean("gfxOrder", "archived", isArchived, idOrder);
		 * return true;
		 * } catch (SQLException e) {
		 * e.printStackTrace();
		 * return false;
		 * }
		 */
	}
/*
	public static void addPerson(User currUser) {
		int newPerson = addLong("person", "SSO", currUser.SSO);

		alterString("person", "firstName", currUser.firstName, newPerson);
		alterString("person", "lastName", currUser.lastName, newPerson);
		alterString("person", "emailAddress", currUser.emailAddress, newPerson);
		// alterString("person","username", currUser.username, newPerson);
		// alterString("person","password", currUser.password, newPerson);
		alterBoolean("person", "isOrderer", currUser.isOrderedBy, newPerson);
		alterBoolean("person", "isResearch", currUser.isResearch, newPerson);
		alterBoolean("person", "isLegal", currUser.isLegal, newPerson);
		alterBoolean("person", "isPhotoEdit", currUser.isPhotoEdit, newPerson);
		alterBoolean("person", "isFootage", currUser.isFootage, newPerson);
		alterBoolean("person", "isArtist", currUser.isArtist, newPerson);
		alterBoolean("person", "isProducer", currUser.isProducer, newPerson);
		alterBoolean("person", "isAdmin", currUser.isAdmin, newPerson);
	}
*/
	public static User getUser(int idPerson) {
		return Users.allUsers.get(idPerson);
	}

	public static void setUser(User newUser, String columnName, int idOrder) throws SQLException {
		GFXSQL.alterInt("gfxOrder", columnName, newUser.getId(), idOrder);
	}

	public static void setSSO(User currUser, long sSO) {
		alterLong("person", "SSO", sSO, currUser.getId());
	}

	public static Destination getDestination(int idDestination) {
		return Destinations.allDestinations.get(idDestination);
	}

	public static void addDestination(Destination currDestination) {
		int newDestination = addString("destination", "name", currDestination.getName());
	}

	public static void addGraphicType(GraphicType currGraphicType) {
		int newGraphicType = addString("graphicType", "name", currGraphicType.getName());
		alterString("graphicType", "prefix", currGraphicType.getPrefix().toUpperCase(), newGraphicType);
		alterInt("graphicType", "idShowName", currGraphicType.getShowName().getId(), newGraphicType);
	}

	public static GraphicType getGraphicType(int idGraphicType) {
		return GraphicTypes.allGraphicTypes.get(idGraphicType);
	}

	public static void refreshConnections() {
		// Trace.info("Checking for other connections...");
		connectedUsers = new ArrayList<User>();
		connectedIPs = new ArrayList<String>();

		try {
			ResultSet rawConnections = GFXSQL.getResultSet("Connection", "*", "");
			while (rawConnections.next()) {
				// Trace.info(Users.currentUser.IP_local + "   " + Users.currentUser.IP + "    " + currUser.IP);
				// if (!currUser.IP.equals(Users.currentUser.IP_local)) {
				String currIP = rawConnections.getString("IP");
				boolean inactive = rawConnections.getBoolean("inactive");
				if (!Users.currentUser.getIP_local().equals(currIP)) {
					if (!inactive) {
						User currUser = Users.allUsers.get(rawConnections.getInt("idPerson"));
						currUser.setIP(rawConnections.getString("IP"));
						connectedUsers.add(currUser);
						connectedIPs.add(currIP);
					}
				} else if (inactive) {
					GFXSQL.alterBoolean("Connection", "inactive", false, Users.currentUser.getIP_local());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		CommDialog.refreshConnectionList();
	}

	public static void addShowName(ShowName currShowName) {
		int newShowName = addString("showName", "name", currShowName.name);
		alterString("showName", "nickName", currShowName.nickName.toUpperCase(), newShowName);
	}
	/*
	public static ShowName getShowName(int idOrder) throws SQLException {
		int idShowName = getInt("gfxOrder", "idShowName", "idOrder=" + idOrder + "")[0];
		return ShowNames.allShowNames.get(idShowName);
	}

	public static IndexedSQL<String> getSummary(int idOrder) throws SQLException {
		IndexedSQL<String> newSummary = new IndexedSQL<String>();
		newSummary.setID(idOrder);
		newSummary.setElement(getString("gfxOrder", " summary", idOrder + "")[0]);
		return newSummary;
	}

	public static BLOCK getBlock(int idOrder) throws SQLException {
		ResultSet rawOrder = SQLUtility.getResultSetFromActiveDB("gfxOrder", "*", "idOrder = " + idOrder);
		if (rawOrder.next()) {
			return BLOCK.fromString(rawOrder.getString("block"));
		} else {
			return null;
		}
	}
*/
	public static ArrayList<PreApprovedText> getPreApprovalList() {
		ResultSet rs = SQLUtility.getResultSetFromActiveDB("preApproval ORDER BY entry", "*", "");
//		NamedList<IndexedSQL<String>> list = new NamedList<IndexedSQL<String>>();
		ArrayList<PreApprovedText> list = new ArrayList<PreApprovedText>();

		try {
			while (rs.next()) {
				PreApprovedText newText = new PreApprovedText();
				newText.fromSQL(rs.getInt("idpreApproval"));
				list.add(newText);
//				IndexedSQL<String> indexedSQL;
//				indexedSQL = new IndexedSQL<String>(rs.getInt("idpreApproval"), rs.getString("entry"));
//				list.add(indexedSQL);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	public static void removeViewPreset(ViewFilterPreset currIndexedViewPreset) {
		Integer[] idViewFilterList = GFXSQL.getInt("viewPreset","idViewFilterList","idViewPreset=" + currIndexedViewPreset.getId());
		for(Integer currListID : idViewFilterList){
			GFXSQL.removeElement("ViewFilterList", "idViewFilterList=" + currListID);
		}
		GFXSQL.removeElement("viewPreset", "idViewPreset=" + currIndexedViewPreset.getId());
	}
}
